package cn.com.jsepc.rep.report;

import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.util.Hashtable;
import javax.servlet.ServletException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import cn.com.jsepc.rep.catalog.CatalogDAO;
import cn.com.jsepc.rep.comm.Common;
import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.vo.ReportVO;

import com.jarp.db.DAO;

public class shuju_tianbao {

	 // 加载数据
	public String getDemeGrid(String start, String limit, String KeyWord_0,
			String KeyWord_1, String KeyWord_2, String KeyWord_3,
			String KeyWord_4,String KeyWord_5,String roleid,String KeyWord_6) {
		String res = "";
		DAO dao = new DAO();
		try {
			List<ReportVO> lstResults = new ArrayList<ReportVO>();
			int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iCurPage = iStart/iPageSize + 1;
//            System.out.println("iCurPage:"+iCurPage);
			StringBuffer str = new StringBuffer();
			str.append("select a.*,c.CATANAME,t.roleid,d.NAME DEPTNAME from (select * from TB_REPORT a where ISVALID=1 and a.REFFACT is not null and a.REFFACT <> ' ' ");
			//点击树的情况
			if(KeyWord_5 != null && !KeyWord_5.equals(""))
			{
				//点击树根节点的情况
				if(KeyWord_5.equals("0")){
					if (KeyWord_0 != null && !KeyWord_0.equals("")) {
						str.append(" and a.REPNAME like '%" + KeyWord_0 + "%'");
					}
					if (KeyWord_1 != null && !KeyWord_1.equals("") && !KeyWord_1.equals("101")) {
						str.append(" and a.DEPARTMENT = '" + KeyWord_1 + "'");
					}
					if (KeyWord_2 != null && !KeyWord_2.equals("")) {
						if(KeyWord_2.equals("3")){
						}else{
							str.append(" and a.SOURCETYPE = " + KeyWord_2);
						}
					}
					if (KeyWord_3 != null && !KeyWord_3.equals("")) {
						if(KeyWord_3.equals("6")){
						}else{
							str.append(" and a.DATATYPE = " + KeyWord_3);
						}
					}
					if (KeyWord_4 != null && !KeyWord_4.equals("")) {
						if(KeyWord_4.equals("7")){
						}else{
							str.append(" and a.UPDATETIME = " + KeyWord_4);
						}
					}if (KeyWord_6 != null && !KeyWord_6.equals("")) {		
						str.append(" and a.REPCODE like '%" + KeyWord_6.toUpperCase() + "%'");
					}
				}
				//点击树子节点的情况
				else{
				if (KeyWord_0 != null && !KeyWord_0.equals("")) {
					str.append(" and a.REPNAME like '%" + KeyWord_0 + "%'");
				}
				if (KeyWord_1 != null && !KeyWord_1.equals("") && !KeyWord_1.equals("101")) {
					str.append(" and a.DEPARTMENT = '" + KeyWord_1 + "' ");
				}
				if (KeyWord_2 != null && !KeyWord_2.equals("")) {
					if(KeyWord_2.equals("3")){
					}else{
						str.append(" and a.SOURCETYPE = " + KeyWord_2 );
					}
				}
				if (KeyWord_3 != null && !KeyWord_3.equals("")) {
					if(KeyWord_3.equals("6")){
					}else{
						str.append(" and a.DATATYPE = " + KeyWord_3 );
					}
				}
				if (KeyWord_4 != null && !KeyWord_4.equals("")) {
					if(KeyWord_4.equals("7")){
					}else{
						str.append(" and a.UPDATETIME = " + KeyWord_4 );
					}
				}
				if (KeyWord_6 != null && !KeyWord_6.equals("")) {		
					str.append(" and a.REPCODE like '%" + KeyWord_6.toUpperCase() + "%'");
				}
				if (KeyWord_5 != null && !KeyWord_5.equals("")) {
					//str.append(" and a.CATAID = " + KeyWord_5);
					String catid = new CatalogDAO().getAllCatatID(KeyWord_5);
					str.append(" and a.CATAID in ( " + catid+") ");
				}							
			}
				}
			//未点击树的情况
			else{
				if (KeyWord_0 != null && !KeyWord_0.equals("")) {
					str.append(" and REPNAME like '%" + KeyWord_0 + "%'");
				}
				if (KeyWord_1 != null && !KeyWord_1.equals("") && !KeyWord_1.equals("101")) {
					str.append(" and DEPARTMENT = '" + KeyWord_1 + "'");
				}
				if (KeyWord_2 != null && !KeyWord_2.equals("")) {
					if(KeyWord_2.equals("3")){
					}else{
						str.append(" and SOURCETYPE = " + KeyWord_2);
					}
				}
				if (KeyWord_3 != null && !KeyWord_3.equals("")) {
					if(KeyWord_3.equals("6")){
					}else{
						str.append(" and DATATYPE = " + KeyWord_3);
					}
				}
				if (KeyWord_4 != null && !KeyWord_4.equals("")) {
					if(KeyWord_4.equals("7")){
					}else{
						str.append(" and UPDATETIME = " + KeyWord_4);
					}
				}
				if (KeyWord_6 != null && !KeyWord_6.equals("")) {		
					str.append(" and a.REPCODE like '%" + KeyWord_6.toUpperCase() + "%'");
				}
//				if (KeyWord_5 != null && !KeyWord_5.equals("")) {
//					str.append(" and a.CATAID = " + KeyWord_5);
//				}
			}
			String roleids = new Common().toInStr(roleid);
            str.append(" ) a");
            str.append(" left join TB_ROLE_REPORT t on t.roleid in ("+roleids+") and t.repid = a.repid");
            str.append(" left join TB_CATALOG c on c.CATAID = a.CATAID");
            str.append(" left join TB_CORP_DEPT d on d.ID=a.DEPARTMENT ");
            str.append(" where t.roleid is not null");
//			str.append(" where rn between " + iStart + " and " + iEnd);
                         System.out.println(str.toString());
			Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(),iCurPage,iPageSize);
			if (v != null) {
				for (int i = 0; i < v.size(); i++) {
					Hashtable hashRows = (Hashtable) v.get(i);
					ReportVO repVO = new ReportVO();
					repVO.setRepid((String) hashRows.get("REPID"));
					repVO.setRepcode((String) hashRows.get("REPCODE"));
					repVO.setRepname((String) hashRows.get("REPNAME"));
					repVO.setCataid((String) hashRows.get("CATANAME"));
					repVO.setRepdesc((String) hashRows.get("REPDESC"));
					repVO.setDesc((String) hashRows.get("CALDESC"));
					repVO.setDataprec((String) hashRows.get("DATAPREC"));
					repVO.setDataunit((String) hashRows.get("DATAUNIT"));
					repVO.setDepartmentid((String) hashRows.get("DEPARTMENT"));
					repVO.setDepartment((String) hashRows.get("DEPTNAME"));
					repVO.setSourcetype((String) hashRows.get("SOURCETYPE"));
					repVO.setDatatype((String) hashRows.get("DATATYPE"));
					repVO.setUpdatetime((String) hashRows.get("UPDATETIME"));
					repVO.setReffact((String) hashRows.get("REFFACT"));
					repVO.setRepvaluecol((String) hashRows.get("REPVALUECOL"));
                    repVO.setIsselect((String)hashRows.get("ROLEID"));
					lstResults.add(repVO);

				}
			}
			JSONArray json = new JSONArray();
			StringBuffer str1 = new StringBuffer();
			str1.append("select count(1) CNT from (");
			str1.append(str);
			str1.append(" ) z ");
			String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
			json = JSONArray.fromObject(lstResults);
			res = "{totalCount:" + totalCount + ",data:" + json.toString()
					+ "}";
		} catch (Exception ex) {
			ex.printStackTrace();
			System.out.println("NewProjectTree getXmDl Exception.");
		}
		return res;
	}
        
}
